package keye.document_manage.util;

import java.util.List;
import java.util.Map;

import keye.document_manage.domain.TableInterfaceListView;
import keye.document_manage.entity.SelectParameters;

public class MySqlProvider {

	public final static String FILED_TYPE = "filed_type";
	public final static String FILED_NAME = "filed_name";

	public String executeSQL(Map<String, Object> para){
		System.out.println( para.get("sql"));
		return (String) para.get("sql");
	}
	
	public String getList(Map<String, Object> para){
		return getListWithParasByPage(para);
	}
	
	@SuppressWarnings("unchecked")
	public String getListWithDiyParasByPage(Map<String, Object> para){
		StringBuilder sb = new StringBuilder();
		String tableName = (String) para.get("tableName");

		List<TableInterfaceListView> tableFileds = (List<TableInterfaceListView>) para.get("tableFiled");

		List<SelectParameters> where = null;
		try {	
			where = (List<SelectParameters>)para.get("where");
		} catch (org.apache.ibatis.binding.BindingException e) {
			System.out.println("没有查询参数");
		}


		sb.append("select ");
		if(null == tableFileds || tableFileds.size()==0){
			sb.append(" * ");
		}else{
			for(TableInterfaceListView pm : tableFileds){
				sb.append(pm.getFieldName()+",");
			}
			sb.deleteCharAt(sb.length()-1);
		}
		sb.append(" from "+tableName);
		sb.append(" where 1=1");
		if(null != where )
			for (int i = 0, length = where.size(); i < length; i++) {
				sb.append(" and "+solveParameters(where.get(i)));
			}
		String diyWhere="";
		try {	
			diyWhere = (String) para.get("diyWhere");
		} catch (org.apache.ibatis.binding.BindingException e) {
			System.out.println("没有查询参数");
		}
		sb.append(" "+diyWhere);
		
		System.out.println("getListWithParasByPage Sql："+sb.toString());
		return sb.toString();
	}
	@SuppressWarnings("unchecked")
	public String getListWithParasByPage(Map<String, Object> para){
		StringBuilder sb = new StringBuilder();
		String tableName = (String) para.get("tableName");

		List<TableInterfaceListView> tableFileds = (List<TableInterfaceListView>) para.get("tableFiled");

		List<SelectParameters> where = null;
		try {	
			where = (List<SelectParameters>)para.get("where");

		} catch (org.apache.ibatis.binding.BindingException e) {
			System.out.println("没有查询参数");
		}


		sb.append("select ");
		if(null == tableFileds || tableFileds.size()==0){
			sb.append(" * ");
		}else{
			for(TableInterfaceListView pm : tableFileds){
				sb.append(pm.getFieldName()+",");
			}
			sb.deleteCharAt(sb.length()-1);
		}
		sb.append(" from "+tableName);
		sb.append(" where 1=1");
		if(null != where )
			for (int i = 0, length = where.size(); i < length; i++) {
				sb.append(" and "+solveParameters(where.get(i)));
			}
//		sb.append(" order by piece_no");//加上排序规则

		System.out.println("getListWithParasByPage Sql："+sb.toString());
		return sb.toString();
	}

	public static String solveParameters(SelectParameters parameters){
		StringBuilder sb =  new StringBuilder();
		sb.append(" "+parameters.getName());
		sb.append(" "+changeOpration(parameters.getOpreation()));
		sb.append(" "+changeType(parameters.getType()).replace("v", parameters.getValue().toString()));
		return sb.toString();
	}

	public static String changeOpration(String op){

		switch (op.toLowerCase()) {
		case "equal":
			op = " = ";
			break;

		default:
			
//			op = "=";
			break;
		}
		return op;
	}
	public static String changeType(String ty){
		switch (ty.toLowerCase()) {
		case "varchar":
			ty = " 'v' ";
			break;
		case "int":
			ty = " v ";
			break;
		default:
			ty = " v ";
			break;
		}
		return ty;
	}

	@SuppressWarnings("unchecked")
	public String getType(Map<String, Object> para){
		StringBuilder sb = new StringBuilder();
		try {
			String tableName = (String) para.get("tableName");
			List<SelectParameters> selectParameters = (List<SelectParameters>)para.get("selectParas");
			sb.append("select st.name as '"+FILED_TYPE+"',sc.name as '"+FILED_NAME+"' from syscolumns sc,systypes st "); 
			sb.append(" where sc.xtype=st.xtype ");
			sb.append(" and sc.id in( " );
			/**
			 * select st.name as 'TypeName',sc.name,sc.length from syscolumns sc,systypes st 
				where sc.xtype=st.xtype
				and sc.id in(
				select id from sysobjects where xtype='U' and name='TableName')
			 */
			sb.append(" select id from sysobjects  where xtype='U' and name= '"+tableName+"' )");
			if(null != selectParameters && selectParameters.size()!=0){
				sb.append(" and (");
				for(SelectParameters pm : selectParameters){
					sb.append(" sc.name= '"+pm.getName()+"' or ");
				}
				int length = sb.lastIndexOf("or");
				sb.delete(length, length+2);
				sb.append(" )");
			}
		} catch (org.apache.ibatis.binding.BindingException e) {
			System.out.println("没有查询字段，默认所有字段");
		}
		System.out.println("getType Sql："+sb.toString());
		return sb.toString();
	}


	//获取显示字段
	public String getParamters(Map<String, Object> para){
		StringBuilder sb =  new StringBuilder();
		sb.append("select * from table_interface_list_view");
		sb.append(" where table_name = '"+para.get("tableName")+"' ");
		System.out.println("getParamters Sql："+sb.toString());
		return sb.toString();
	}

	public String getWithParas(Map<String, Object> para){
		StringBuilder sb = new StringBuilder();
		try {	
			String tableName = (String) para.get("tableName");
			@SuppressWarnings("unchecked")
			List<SelectParameters> where = (List<SelectParameters>)para.get("where");
			sb.append("select * ");
			sb.append(" from "+tableName);
			sb.append(" where 1=1");
			if(null != where )
				for (int i = 0, length = where.size(); i < length; i++) {
					sb.append(" and "+solveParameters(where.get(i)));
				}
			
		} catch (org.apache.ibatis.binding.BindingException e) {
			System.out.println("没有查询参数");
		}
		System.out.println("getWithParas Sql："+sb.toString());
		return sb.toString();
	}
	
	public String getAllByTableAndRecId(Map<String, Object> para){
		StringBuilder sb = new StringBuilder();
		try {	
			String tableName = (String) para.get("tableName");
			@SuppressWarnings("unchecked")
			List<String> where = (List<String>)para.get("recids");
			sb.append("select * from is_files where 1=1");
			if(null != tableName && !"".equals(tableName)){
				sb.append(" and ( tble_name ='"+tableName+"' )");
			}
			if(null != where && where.size()> 0){
				sb.append(" and (");
				where.forEach(recid->sb.append(" rec_id ='"+recid+"' or"));
				sb.delete(sb.length()-2, sb.length());
				sb.append(")");
			}
			
		} catch (org.apache.ibatis.binding.BindingException e) {
			System.out.println("没有参数");
		}
		System.out.println("getAllByTableAndRecId Sql："+sb.toString());
		return sb.toString();
	}
	
	public String getAllByTableRecId(Map<String, Object> para){
		StringBuilder sb = new StringBuilder();
		try {	
			String tableName = (String) para.get("tableName");
			@SuppressWarnings("unchecked")
			List<String> ids = (List<String>)para.get("recids");
			sb.append("select * from is_files where 1=1");
			if(null != tableName && !"".equals(tableName)){
				sb.append(" and ( tble_name ='"+tableName+"' )");
			}
			if(null != ids && ids.size()> 0){
				sb.append(" and rec_id in ( ");
				ids.forEach(v->sb.append(v+" ,"));
				sb.deleteCharAt(sb.length()-1);
				sb.append(" )");
//				where.forEach(recid->sb.append(" rec_id ='"+recid+"' or"));
//				sb.delete(sb.length()-2, sb.length());
//				sb.append(")");
			}
			
		} catch (org.apache.ibatis.binding.BindingException e) {
			System.out.println("没有参数");
		}
		System.out.println("getAllByTableAndRecId Sql："+sb.toString());
		return sb.toString();
	}
	
	public String getFilesByRecid(Map<String,Object> para){
		StringBuilder sb = new StringBuilder();
		try {	
			String[] tableNames = (String[]) para.get("tableNames");
			@SuppressWarnings("unchecked")
			List<SelectParameters> where = (List<SelectParameters>)para.get("where");
			sb.append("select * from is_files as t0");
			if(null == tableNames || tableNames.length<=0||null == where ){
				return sb.toString();
			}
			if(tableNames.length==1){
				sb.append(" ,(select recid from "+tableNames[0]+" as t1 where 1=1 ");
				where.forEach(v->sb.append(" and "+solveParameters(v)));
			}else {
				sb.append(" select recid from "+tableNames[0]+" as t1,( ");
				sb.append(" select folder_no from "+tableNames[1]+" where 1=1 ");
				where.forEach(v->sb.append(" and "+solveParameters(v)));
				sb.append(" group by folder_no) as t2 where t1.folder_no=t2.folder_no ");
			}
			sb.append(" ) as t where  t0.rec_id=t.recid and t0.tble_name='"+tableNames[0]+"'");
			
		} catch (org.apache.ibatis.binding.BindingException e) {
			System.out.println("没有参数");
		}
		System.out.println("getFilesByRecid Sql："+sb.toString());
		return sb.toString();
	}
	
	//cdg 2016-2-18 16:28:45
	public String getFilesTotalLen(Map<String,Object> para){
		StringBuilder sb = new StringBuilder();
		try {	
			String tableName = (String) para.get("tableName");
			String ids = (String)para.get("recids");
			sb.append("select sum(file_len) from is_files where 1=1");
			if(null != tableName && !"".equals(tableName)){
				sb.append(" and ( tble_name ='"+tableName+"' )");
			}
			if(null != ids && ids.length()> 0){
				sb.append(" and rec_id in ( "+ids+" )");
			}
			
		} catch (org.apache.ibatis.binding.BindingException e) {
			System.out.println("没有参数");
		}
		System.out.println("getFilesTotalLen Sql："+sb.toString());
		return sb.toString();
	}
	
	//cdg 2016-2-29 09:26:48
	public String getFilePathFiled(Map<String,Object> para){
		StringBuilder sb = new StringBuilder();
		try {	
			String tableName = (String) para.get("tableName");
			String ids = (String)para.get("recids");
			sb.append("SELECT a.archive_no ,a.maintitle,t.tble_name,t.os_position,t.file_loc,t.file_name,t.file_id ,t.file_type FROM is_files AS t ");
			if(null == tableName || "".equals(tableName)){
				return "";
			}
			if(null == ids || ids.length()<= 0){
				return "";
			}
			sb.append(" ,"+tableName+" AS a WHERE 1=1 ");
			sb.append(" and a.recid in ( "+ids+" ) and a.recid = t.rec_id order by a.archive_no DESC");
		} catch (org.apache.ibatis.binding.BindingException e) {
			System.out.println("没有参数");
		}
		System.out.println("getFilePathFiled Sql："+sb.toString());
		return sb.toString();
	}
	
	/**
	 * @author cdg 2016年3月1日16:09:08
	 */
	public String getFilesTotalSize(Map<String,Object> para){
		StringBuilder sb = new StringBuilder();
		try {	
			@SuppressWarnings("unchecked")
			List<SelectParameters> where = (List<SelectParameters>)para.get("where");
			String[] tableNames = (String[]) para.get("tableNames");
			if(null == tableNames || tableNames.length<=0||null == where ){
				return "";
			}
			/*
			 * select sum(file_len) as totalSize from is_files ,(
			 *	select recid from archive_description as t1,(
			 *		select folder_no from folder_description where filing_year=1988
			 *		)  as t 
			 *		where t1.folder_no=t.folder_no ) as t2 where  rec_id=t2.recid  
			 */
			sb.append(" select sum(file_len) as totalSize from is_files as t0,( ");
			if(tableNames.length==1){
				sb.append(" select recid from "+tableNames[0]+" as t1 where 1=1 ");
				where.forEach(v->sb.append(" and "+solveParameters(v)));
			}else {
				sb.append(" select recid from "+tableNames[0]+" as t1,( ");
				sb.append(" select folder_no from "+tableNames[1]+" where 1=1 ");
				where.forEach(v->sb.append(" and "+solveParameters(v)));
				sb.append(" group by folder_no) as t2 where t1.folder_no=t2.folder_no ");
			}
			sb.append(" ) as t where  t0.rec_id=t.recid ");
			
		} catch (org.apache.ibatis.binding.BindingException e) {
			e.printStackTrace();
		}
		System.out.println("getFilesTotalSize Sql："+sb.toString());
		return sb.toString();
	}
	
	/**
	 * @author cdg 2016年3月1日16:22:20 
	 */
	public String getFilePathInfo(Map<String,Object> para){
		StringBuilder sb = new StringBuilder();
		try {	
			@SuppressWarnings("unchecked")
			List<SelectParameters> where = (List<SelectParameters>)para.get("where");
			String[] tableNames = (String[]) para.get("tableNames");
			if(null == tableNames || tableNames.length<=0||null == where ){
				return "";
			}
			/*
			 *	select t3.archive_no ,t3.maintitle,t.tble_name,t.os_position,t.file_loc,t.file_name,t.file_id ,t.file_type from is_files as t,(
			 *	select archive_no ,maintitle,recid from archive_description as t1,(
			 *		select folder_no from folder_description where filing_year=1988
			 *		)  as t2 
			 *		where t1.folder_no=t2.folder_no 
			 *	) as t3 
			 *	where  rec_id=t3.recid ORDER BY t3.archive_no
			 */
			sb.append(" select t.archive_no ,t.maintitle,t0.tble_name,t0.os_position,t0.file_loc,t0.file_name,t0.file_id ,t0.file_type from is_files as t0,( ");
			if(tableNames.length==1){
				sb.append(" select archive_no ,maintitle,recid from "+tableNames[0]+" as t1 where 1=1 ");
				where.forEach(v->sb.append(" and "+solveParameters(v)));
			}else {
				sb.append(" select archive_no ,maintitle,recid from "+tableNames[0]+" as t1,( ");
				sb.append(" select folder_no from "+tableNames[1]+" where 1=1 ");
				where.forEach(v->sb.append(" and "+solveParameters(v)));
				sb.append(" group by folder_no ) as t2 where t1.folder_no=t2.folder_no ");
			}
			sb.append(" ) as t where  t0.rec_id=t.recid ORDER BY t.archive_no");
			
		} catch (org.apache.ibatis.binding.BindingException e) {
			e.printStackTrace();
		}
		System.out.println("getFilePathInfo Sql："+sb.toString());
		return sb.toString();
	}
}
